﻿Public Class frmXLSCompanyUp

#Region "이벤트"

    Private Sub btnXlsOpen_Click(sender As System.Object, e As System.EventArgs) Handles btnXlsOpen.Click
        Call gSubImportXLS(grdTmpXlsCompany)
        lbDupRslt.Text = ""
    End Sub

    Private Sub btnCheckData_Click(sender As System.Object, e As System.EventArgs) Handles btnCheckData.Click
        Call companyDupCheck()
    End Sub

    Private Sub btnInsertData_Click(sender As System.Object, e As System.EventArgs) Handles btnInsertData.Click
        Call appendCompany()
    End Sub

    Private Sub PictureBox_Click(sender As System.Object, e As System.EventArgs) Handles PictureBox.Click
        Process.Start("http://blog.naver.com/hooshim/220129866491")
    End Sub

    Private Sub linkLb_LinkClicked(sender As System.Object, e As System.Windows.Forms.LinkLabelLinkClickedEventArgs) Handles linkLb.LinkClicked
        Process.Start("http://www.jadesoft.co.kr/거래처샘플.xls")
    End Sub
#End Region

#Region "프로시져"

    '## 중복체크 
    Private Sub companyDupCheck()
        Call dbConnection()
        Dim corrCnt As Integer = 0
        Dim inCorrCnt As Integer = 0

        For i = 0 To grdTmpXlsCompany.Rows.Count - 1

            Dim tmpSql = "SELECT count(*) FROM tblCompany WHERE compNo='" + grdTmpXlsCompany.Rows(i).Cells(2).Value + "' AND flag<>'N'"
            Dim isCompany As String = ""
            isCompany = gAFieldValue(tmpSql)
            If isCompany = "0" Then
                grdTmpXlsCompany.Rows(i).Cells(0).Value = True
                corrCnt += 1
            Else
                grdTmpXlsCompany.Rows(i).DefaultCellStyle.BackColor = Color.DarkOrange
                inCorrCnt += 1
            End If
        Next
        linkLb.Visible = False
        lbDupRslt.Text = "유효 : " + corrCnt.ToString + " / 중복 : " + inCorrCnt.ToString + " / 총 : " + grdTmpXlsCompany.Rows.Count.ToString

        dbConn.Close()
    End Sub

    '## 데이터 적용
    Private Sub appendCompany()
        Dim appendCompCnt As Integer = 0
        Dim company, compNo, owner, phone, fax, major, cellPhone, addr, types, article As String
        Dim curDt As DateTime = Now

        If grdTmpXlsCompany.Rows.Count = 0 Then
            MsgBox("거래처로 적용할 데이터가 없습니다.")
            Exit Sub
        End If
        If grdTmpXlsCompany.Rows(0).Cells.Count <> 11 Then
            MsgBox("엑셀 업로드를 위한 거래처 양식이 정상적이지 않습니다.")
            Exit Sub
        End If

        Call dbConnection()

        For i = 0 To grdTmpXlsCompany.Rows.Count - 1
            company = ""
            compNo = ""
            owner = ""
            phone = ""
            fax = ""
            major = ""
            cellPhone = ""
            addr = ""
            types = ""
            article = ""
            If grdTmpXlsCompany.Rows(i).Cells(0).Value.ToString = "True" Then
                company = gFnChkVal(grdTmpXlsCompany.Rows(i).Cells(1).Value.ToString)
                compNo = gFnChkVal(grdTmpXlsCompany.Rows(i).Cells(2).Value.ToString)
                owner = gFnChkVal(grdTmpXlsCompany.Rows(i).Cells(3).Value.ToString)
                phone = gFnChkVal(grdTmpXlsCompany.Rows(i).Cells(4).Value.ToString)
                fax = gFnChkVal(grdTmpXlsCompany.Rows(i).Cells(5).Value.ToString)
                major = gFnChkVal(grdTmpXlsCompany.Rows(i).Cells(6).Value.ToString)
                cellPhone = gFnChkVal(grdTmpXlsCompany.Rows(i).Cells(7).Value.ToString)
                addr = gFnChkVal(grdTmpXlsCompany.Rows(i).Cells(8).Value.ToString)
                types = gFnChkVal(grdTmpXlsCompany.Rows(i).Cells(9).Value.ToString)
                article = gFnChkVal(grdTmpXlsCompany.Rows(i).Cells(10).Value.ToString)
                Dim compSql As String
                compSql = "INSERT INTO tblCompany(company, compNo, owner, phone, fax, major, cellPhone, addr, types, article, addDt) " + _
                             " VALUES('" + company + "', '" + compNo + "', '" + owner + "', '" + phone + "', '" + fax + "', '" + major + "', '" + cellPhone + "', '" + addr + "', '" + types + "', '" + article + "','" + curDt + "')"
                Call gSubSaveAction(compSql, "")
                appendCompCnt += 1
            End If
        Next

        dbConn.Close()
        If appendCompCnt = 0 Then
            MsgBox("중복체크를 실행해 주시기 바랍니다.")
            btnCheckData.Focus()
            Exit Sub
        End If
        MsgBox("거래처 정보가 등록되었습니다")
    End Sub
#End Region

End Class